package com.ruoyi.web.excel;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.fastjson2.JSON;
import com.ruoyi.common.core.domain.entity.SysDept;
import com.ruoyi.common.core.domain.entity.SysDictData;
import com.ruoyi.system.mapper.SysDeptMapper;
import com.ruoyi.system.mapper.SysDictDataMapper;
import com.ruoyi.web.constant.Constants;
import com.ruoyi.web.domain.WorkForm;
import com.ruoyi.web.domain.dto.WorkFormExcelTemplateDto;
import com.ruoyi.web.domain.importEntity.ImportError;
import com.ruoyi.web.domain.importEntity.ImportResult;
import com.ruoyi.web.service.WorkFormService;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;
import java.util.concurrent.ThreadLocalRandom;
import java.util.stream.Collectors;

/**
 * description:
 * auth fcb
 * date 2025-06-03 16:33
 **/
@Slf4j
public class WorkFormExcelTemplateListener implements ReadListener<WorkFormExcelTemplateDto> {

    private static final int BATCH_COUNT = 200;

    private List<WorkForm> cachedDataList = new ArrayList<>(BATCH_COUNT);

    private WorkFormService workFormService;

    private List<SysDept> deptList;

    private String createBy;

    private DataSourceTransactionManager transactionManager;

    private TransactionStatus transactionStatus = null;

    private SysDeptMapper sysDeptMapper;

    private String problemType;

    /**
     * 错误信息收集
     */
    private List<ImportError> importErrors = new ArrayList<>();

    /**
     * 导入结果
     */
    @Getter
    private ImportResult importResult = new ImportResult();

    private SysDictDataMapper dictDataMapper;

    public WorkFormExcelTemplateListener() {}

    public WorkFormExcelTemplateListener(SysDictDataMapper dictDataMapper, WorkFormService workFormService, List<SysDept> deptList, String createBy, DataSourceTransactionManager transactionManager, SysDeptMapper sysDeptMapper, String problemType) {
        this.workFormService = workFormService;
        this.deptList = deptList;
        this.createBy = createBy;

        this.transactionManager = transactionManager;
        DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
        transactionDefinition.setIsolationLevel(DefaultTransactionDefinition.ISOLATION_DEFAULT);
        transactionDefinition.setPropagationBehavior(DefaultTransactionDefinition.PROPAGATION_REQUIRED);
        this.transactionStatus = transactionManager.getTransaction(transactionDefinition);

        this.sysDeptMapper = sysDeptMapper;
        this.problemType = problemType;
        this.dictDataMapper = dictDataMapper;
    }


    @Override
    public void invoke(WorkFormExcelTemplateDto data, AnalysisContext analysisContext) {
        // 获取 Excel 行号
        int currentRowIndex = analysisContext.readRowHolder().getRowIndex() + 1;
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        // 总处理条数 + 1
        importResult.setTotalCount(importResult.getTotalCount() + 1);

        // 数据校验
        String validationError = validateData(data, currentRowIndex);
        if (validationError != null) {
            // 记录错误信息
            ImportError error = new ImportError(currentRowIndex, data.getEnterpriseName(), validationError);
            importErrors.add(error);
            log.warn("第{}行数据校验失败: {}", currentRowIndex, validationError);
            return;
        }

        // 业务校验
        String businessError = validateBusiness(data, currentRowIndex);
        if (businessError != null) {
            ImportError error = new ImportError(currentRowIndex, data.getEnterpriseName(), businessError);
            importErrors.add(error);
            log.warn("第{}行业务校验失败: {}", currentRowIndex, businessError);
            return;
        }

        // 数据转换
        try {
            WorkForm workForm = this.convertData(data);
            cachedDataList.add(workForm);

//            if (cachedDataList.size() >= BATCH_COUNT) {
//                this.saveData();
//                cachedDataList = new ArrayList<>(BATCH_COUNT);
//            }


        } catch (Exception e) {
            ImportError error = new ImportError(currentRowIndex, data.getEnterpriseName(), "数据转换异常: " + e.getMessage());
            importErrors.add(error);
            log.error("第{}行数据转换异常", currentRowIndex, e);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 如果事务已经提交或回滚
        if (transactionStatus.isCompleted()) {
            return;
        }

        // 该部分为业务逻辑A：只存储通过校验的数据
//        try {
//            if (!cachedDataList.isEmpty()) {
//                this.saveData();
//            }
//
//            // 设置导入结果
//            importResult.setSuccessCount(importResult.getTotalCount() - importErrors.size());
//            importResult.setErrorCount(importErrors.size());
//            importResult.setErrors(importErrors);
//
//            if (!transactionStatus.isCompleted()) {
//                transactionManager.commit(transactionStatus);
//                log.info("当前事务已提交");
//            }

        // 该部分为业务逻辑B：全部导入数据通过校验后才进行存储数据
        try {
            if (importErrors.isEmpty()) {
                // 若没有任何错误，则保存所有数据
                if (!cachedDataList.isEmpty()) {
                    workFormService.saveBatch(cachedDataList);
                    log.info("所有数据校验通过，成功保存{}条数据", cachedDataList.size());
                }

                // 提交事务
                transactionManager.commit(transactionStatus);
                log.info("事务提交成功");

                // 设置成功结果
                importResult.setSuccessCount(cachedDataList.size());
                importResult.setErrorCount(0);

            } else {
                // 若有错误，则回滚事务，不保存任何数据
                transactionManager.rollback(transactionStatus);
                log.info("存在{}个校验错误，事务已回滚，未保存任何数据", importErrors.size());

                // 设置失败结果
                importResult.setSuccessCount(0);
                importResult.setErrorCount(importErrors.size());
                importResult.setErrors(importErrors);
            }
        } catch (Exception e) {
            log.error("最终处理异常", e);
            this.transactionManager.rollback(transactionStatus);

            // 记录异常错误
            ImportError error = new ImportError(null, "系统异常", "处理过程中发生系统异常: " + e.getMessage());
            importErrors.add(error);
            importResult.setErrorCount(importErrors.size());
            importResult.setErrors(importErrors);
        }
        log.info("诉求导入完成！成功: {}, 失败: {}", importResult.getSuccessCount(), importResult.getErrorCount());
    }

    /**
     * 数据校验
     */
    private String validateData(WorkFormExcelTemplateDto data, int rowNumber) {
        // 1. 必填字段检查
        if (StrUtil.isBlank(data.getEnterpriseName())) {
            return "企业名称不能为空";
        }
        if (StrUtil.isBlank(data.getIndustry())) {
            return "行业不能为空";
        }
        if (StrUtil.isBlank(data.getCity())) {
            return "所在地市不能为空";
        }
        if (StrUtil.isBlank(data.getCounty())) {
            return "所在区县不能为空";
        }
        if (StrUtil.isBlank(data.getPolicyFormType())) {
            return "诉求类型不能为空";
        }
        if (StrUtil.isBlank(data.getPolicyFormSubType())) {
            return "诉求子类不能为空";
        }
        if (StrUtil.isBlank(data.getStatus())) {
            return "服务状态不能为空";
        }
        if (StrUtil.isBlank(data.getProblemDescription())) {
            return "企业期盼及建议不能为空";
        }
        if (StrUtil.isBlank(data.getExpectedCoordinationDeptName())) {
            return "需要协调的部门不能为空";
        }
        if (data.getCreateTime() == null) {
            return "诉求提交时间不能为空";
        }
        if (data.getCompleteTime() == null) {
            return "诉求处理时间不能为空";
        }

//        // 2. 数据长短检查：企业期盼建议（必填字段）以及落实情况（非必填字段）须超过50个字
//        if (data.getProblemDescription().length() <= 50) {
//            return "企业期盼建议填写字数须超过50个字";
//        }
//        if (StrUtil.isNotBlank(data.getSummaryExperience()) && !(data.getSummaryExperience().length() > 50)) {
//            return "落实情况填写字数须超过50个字";
//        }

        // 2. 日期格式校验
        String dateValidationError = validateDateTimeFields(data, rowNumber);
        if (dateValidationError != null) {
            return dateValidationError;
        }

        // 3. 数据格式检查
        if (StrUtil.isNotBlank(data.getContactPhone()) && (!(data.getContactPhone().matches("^(?:(?:\\+|00)86)?1[3-9]\\d{9}$") // 国内11位手机号码
                || data.getContactPhone().matches("^(?:(?:0\\d{2,3}-)?\\d{7,8})(?:-\\d{1,6})?$") // 国内固话
                || data.getContactPhone().matches("^400[-]?\\d{3}[-]?\\d{4}$")))) {
            return "手机号格式不正确";
        }
        // 数据校验通过
        return null;
    }

    /**
     * 日期时间字段校验
     */
    private String validateDateTimeFields(WorkFormExcelTemplateDto data, int rowNumber) {
        try {
            if (data.getCompleteTime() != null) {
                // 检查诉求处理时间是否早于诉求提交时间
                if (data.getCompleteTime().isBefore(data.getCreateTime())) {
                    return "诉求处理时间不能早于诉求提交时间";
                }
            }
        } catch (Exception e) {
            log.error("第{}行日期格式解析异常", rowNumber, e);
            return "日期格式不正确，请使用正确的日期时间格式（如：2025-01-01 09:00）";
        }
        return null;
    }

    /**
     * 业务校验
     */
    private String validateBusiness(WorkFormExcelTemplateDto data, int rowNumber) {
        // 0. 校验诉求是否重复（企业名称+企业期盼及建议 联合校验）
        String param = data.getEnterpriseName().trim() +  data.getProblemDescription().trim();
        boolean isDuplicated = workFormService.isDuplicatedWorkForm(param);
        if (isDuplicated) {
            return "该诉求已存在，请勿重复提交";
        }

        // 1. 校验行业数据的准确性
        List<SysDictData> industryOne = dictDataMapper.selectDictDataByType("industry_one");
        boolean anyMatch1 = industryOne.stream().anyMatch(i -> i.getDictLabel().equals(data.getIndustry()));
        if (!anyMatch1) {
            return "行业不存在: " + data.getIndustry();
        }

        // 2. 校验所在地市的准确性
        List<String> cityDeptIds = sysDeptMapper.selectDeptIdByName(data.getCity());
        if (cityDeptIds == null || cityDeptIds.isEmpty()) {
            return "所在地市不存在: " + data.getCity();
        }

        // 3. 校验所在区县的准确性
        String cityDeptId = cityDeptIds.get(0);
        String countyDeptId = sysDeptMapper.selectDeptIdByParentIdAndName(cityDeptId, data.getCounty());
        if (countyDeptId == null) {
            return "所在区县不存在: " + data.getCounty();
        }

        // 4. 校验诉求类型的准确性
        List<SysDictData> formType = dictDataMapper.selectDictDataByType("form_type");
        List<SysDictData> formTypes = formType.stream().filter(i -> i.getDictLabel().equals(data.getPolicyFormType())).collect(Collectors.toList());
        if (formTypes.isEmpty()) {
            return "诉求类型不存在: " + data.getPolicyFormType();
        }

        // 5. 校验诉求子类的准确性
        Long formSubTypeDictCode = dictDataMapper.selectFormSubTypeByDictLabel(data.getPolicyFormSubType(), formTypes.get(0).getDictValue());
        if (formSubTypeDictCode == null) {
            return "诉求子类不存在: " + data.getPolicyFormSubType();
        }

        // 6. 校验服务状态的准确性
        List<SysDictData> formStatus = dictDataMapper.selectDictDataByType("form_status");
        boolean anyMatch2 = formStatus.stream().anyMatch(i -> i.getDictLabel().equals(data.getStatus()));
        if (!anyMatch2) {
            return "服务状态不存在: " + data.getStatus();
        }

        // 7. 校验需要协调的部门的准确性
        boolean anyMatch3 = deptList.stream().anyMatch(i -> i.getDeptName().equals(data.getExpectedCoordinationDeptName()));
        if (!anyMatch3) {
            return "需要协调的部门不存在: " + data.getExpectedCoordinationDeptName();
        }

        if (StrUtil.isNotBlank(data.getProblemType())) {
            // 8. 校验服务等级的准确性
            List<SysDictData> problemType = dictDataMapper.selectDictDataByType("problem_type");
            boolean anyMatch4 = problemType.stream().anyMatch(i -> i.getDictLabel().equals(data.getProblemType()));
            if (!anyMatch4) {
                return "服务等级不存在: " + data.getProblemType();
            }
        }

        // 业务校验通过
        return null;
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
//        log.error("导入诉求异常，事务回滚");
//        this.transactionManager.rollback(transactionStatus);
//        throw exception;

        // 获取当前行号（由于转换失败，可能无法获取完整行信息）
        Integer rowIndex = null;
        try {
            rowIndex = context.readRowHolder().getRowIndex() + 1;
        } catch (Exception e) {
            // 若行号无法正常获取，设置为未知
            rowIndex = -1;
        }

        String errorMsg;
        String enterpriseName = "";

        // 判断异常类型
        if (exception instanceof com.alibaba.excel.exception.ExcelDataConvertException) {
            ExcelDataConvertException excelException = (ExcelDataConvertException) exception;

            // 获取列号，判断是否是日期字段的列
            Integer columnIndex = excelException.getColumnIndex();
            String columnName = "未知列";

            // 根据列索引判断是哪个字段出了问题
            if (columnIndex != null) {
                switch (columnIndex) {
                    case 14: // createTime
                        columnName = "诉求提交时间";
                        break;
                    case 15: // completeTime
                        columnName = "诉求处理时间";
                        break;
                    default:
                        columnName = "第" + (columnIndex + 1) + "列";
                }
            }

            errorMsg = String.format("%s格式错误，请填写正确的日期时间格式（如：2025-01-01 09:00、2025/01/01 09:00）", columnName);

            // 尝试从原始数据中获取企业名称
            try {
                Object rawData = context.readRowHolder().getCurrentRowAnalysisResult();
                if (rawData instanceof WorkFormExcelTemplateDto) {
                    WorkFormExcelTemplateDto dto = (WorkFormExcelTemplateDto) rawData;
                    if (StrUtil.isNotBlank(dto.getEnterpriseName())) {
                        enterpriseName = dto.getEnterpriseName();
                    }
                }
            } catch (Exception e) {
                // 忽略获取企业名称的异常
            }
        } else {
            // 其他类型的异常
            errorMsg = "数据解析异常: " + exception.getMessage();
        }

        // 记录错误信息
        ImportError error = new ImportError(rowIndex, enterpriseName, errorMsg);
        importErrors.add(error);
        importResult.setSuccessCount(0);
        importResult.setErrorCount(importErrors.size());
        importResult.setErrors(importErrors);

        log.error("第{}行数据解析异常: {}", rowIndex, errorMsg, exception);

        // 回滚事务
        if (transactionStatus != null && !transactionStatus.isCompleted()) {
            transactionManager.rollback(transactionStatus);
            log.info("由于数据解析异常，事务已回滚");
        }

        // 此处不重新抛出异常，而是通过importErrors收集错误信息，在doAfterAllAnalysed中统一处理，避免向上层传递导致500错误
    }

    private void saveData() {
        if (CollectionUtil.isEmpty(cachedDataList)) {
            return;
        }
        try {
            workFormService.saveBatch(cachedDataList);
            log.info("存储数据库成功！成功保存{}条数据", cachedDataList.size());
        } catch (Exception e) {
            // 批量保存失败，记录每条数据的错误
            for (WorkForm failedData : cachedDataList) {
                ImportError error = new ImportError(null, failedData.getEnterpriseName(), "数据保存失败: " + e.getMessage());
                importErrors.add(error);
            }
            // 继续抛出异常，触发事务回滚
            throw e;
        }
    }

    private WorkForm convertData(WorkFormExcelTemplateDto data) {
        WorkForm workForm = new WorkForm();
        workForm.setImportTime(LocalDateTime.now());
        workForm.setEnterpriseName(data.getEnterpriseName());
        workForm.setIndustry(data.getIndustry());
        workForm.setCity(data.getCity());
        workForm.setCounty(data.getCounty());
        workForm.setPolicyFormType(data.getPolicyFormType());
        workForm.setPolicyFormSubType(data.getPolicyFormSubType());
        String status = Constants.WORK_FORM_STATUS_MAP.get(data.getStatus());
        if(status != null) {
            workForm.setStatus(Byte.valueOf(status));
        }else {
            // 待处理
            workForm.setStatus(Byte.valueOf("0"));
        }
        workForm.setProblemDescription(data.getProblemDescription());
        workForm.setContactName(data.getContactName());
        workForm.setContactPhone(data.getContactPhone());
        workForm.setExpectedCoordinationDeptName(data.getExpectedCoordinationDeptName());
        workForm.setExpectedCoordinationDeptId(this.findDeptIdByDeptName(data.getExpectedCoordinationDeptName()));
        workForm.setDealerDeptId(this.doFindDeptId(data.getDealerDeptName()));
        workForm.setDealerDeptName(this.findDealDeptName(data.getDealerDeptName()));
        workForm.setSummaryExperience(data.getSummaryExperience());
        workForm.setCreateBy(createBy);
        // 设置一个从 2025.06.01 到 2025.09.31 之间 9点-18点 的随机时刻
//        workForm.setCreateTime(generateRandomWeekdayLocalDateTime(2025, 6, 1, 2025, 9, 30));
        workForm.setCreateTime(data.getCreateTime());
        workForm.setUpdateTime(workForm.getCreateTime());
        workForm.setSource("其他");

        workForm.setProvince("山东省");
        // 根据服务状态设置其他业务字段.
        //待处理
        if("0".equals(status)) {
            workForm.setIsConfirm("0");
        }
        // 进行中，需要设置省级审核状态为1（通过），派发时间设置为当前时间，
        if("1".equals(status)) {
            workForm.setDispatchAuditStatus("1");
            workForm.setDispatchAuditTime(LocalDateTime.now());
            workForm.setIsConfirm("0");
            workForm.setProblemType(StrUtil.isNotBlank(data.getProblemType()) ? data.getProblemType() : problemType);
        }
        // 待评价，需要设置省级审核状态为1（通过），派发时间设置为当前时间，完成时间，确认及确认时间
        if("2".equals(status)) {
            workForm.setDispatchAuditStatus("1");
            workForm.setDispatchAuditTime(LocalDateTime.now());
//            workForm.setCompleteTime(generateRandomWeekdayByRuleOne(workForm.getCreateTime()));
            workForm.setCompleteTime(data.getCompleteTime());
            workForm.setDealMethod("已处理");
            workForm.setConfirmTime(data.getCompleteTime().minusDays(1));
            workForm.setIsConfirm("1");
            workForm.setProblemType(StrUtil.isNotBlank(data.getProblemType()) ? data.getProblemType() : problemType);
//            workForm.setCompleteTime(LocalDateTime.now());
        }
        // 已完成,需要设置省级审核状态为1（通过），派发时间设置为当前时间，完成时间，确认及确认时间, 评价和评价结果
        if("3".equals(status)) {
            workForm.setDispatchAuditStatus("1");
            workForm.setDispatchAuditTime(LocalDateTime.now());
//            workForm.setCompleteTime(generateRandomWeekdayByRuleOne(workForm.getCreateTime()));
            workForm.setCompleteTime(data.getCompleteTime());
            workForm.setDealMethod("已处理");
            workForm.setConfirmTime(data.getCompleteTime().minusDays(1));
            workForm.setIsConfirm("1");
            workForm.setProblemType(StrUtil.isNotBlank(data.getProblemType()) ? data.getProblemType() : problemType);
            workForm.setEvaluationConfirmation("满意");
            workForm.setEvaluationTime(workForm.getCompleteTime().plusDays(7));
//            workForm.setCompleteTime(LocalDateTime.now());
        }
        return workForm;
    }

    /**
     * 生成随机的工作日时刻
     */
    private LocalDateTime generateRandomWeekdayLocalDateTime(int startYear, int startMonth, int startDay, int endYear, int endMonth, int endDay) {
        // 定义日期范围
        LocalDate startDate = LocalDate.of(startYear, startMonth, startDay);
        LocalDate endDate = LocalDate.of(endYear, endMonth, endDay);

        // 生成随机日期
        LocalDate randomDate;
        do {
            long startEpochDay = startDate.toEpochDay();
            long endEpochDay = endDate.toEpochDay();
            long randomDay = ThreadLocalRandom.current().nextLong(startEpochDay, endEpochDay + 1);
            randomDate = LocalDate.ofEpochDay(randomDay);
        } while (isWeekend(randomDate)); // 跳过周末，确保是工作日

        // 生成随机时间（范围在9:00-18:00之间）
        LocalTime randomTime = generateRandomWorkTime();

        return LocalDateTime.of(randomDate, randomTime);
    }

    /**
     * 规则一：根据某一时刻随机生成其三天后、五天后或七天后的工作日时刻
     */
    private LocalDateTime generateRandomWeekdayByRuleOne(LocalDateTime localDateTime) {
        Random random = new Random();
        // 定义日期的偏移量
        List<Integer> dayOffsets = Arrays.asList(3, 5, 7);
        // 进行随机选择
        Integer randomDayOffset = dayOffsets.get(random.nextInt(dayOffsets.size()));
        // 获取随机的新日期
        LocalDate randomDate = localDateTime.toLocalDate().plusDays(randomDayOffset);
        // 获取随机的工作时间（范围在9:00-18:00之间）
        LocalTime randomTime = generateRandomWorkTime();

        return LocalDateTime.of(randomDate, randomTime);
    }

    private LocalTime generateRandomWorkTime() {
        int startMinutes = 9 * 60;
        int endMinutes = 18 * 60;
        int randomMinutes = ThreadLocalRandom.current().nextInt(startMinutes, endMinutes);

        int hours = randomMinutes / 60;
        int minutes = randomMinutes % 60;
        int seconds = ThreadLocalRandom.current().nextInt(0, 60);
        return LocalTime.of(hours, minutes, seconds);
    }

    /**
     * 判断指定日期是否为周末
     */
    private boolean isWeekend(LocalDate date) {
        return date.getDayOfWeek().getValue() >= 6;
    }

    private String findDeptIdByDeptName(String deptName) {
        if(CollectionUtil.isEmpty(deptList)) {
            return null;
        }
        if(StrUtil.isEmpty(deptName)) {
            return null;
        }
        for(SysDept dept : deptList) {
            if(deptName.equals(dept.getDeptName())) {
                return dept.getDeptId();
            }
        }
        return null;
    }

    private String findDealDeptName(String deptName) {
        if(StrUtil.isEmpty(deptName)) {
            return null;
        }
        if(deptName.contains("-")) {
            String[] split = deptName.split("-");
            return split[1];
        }
        return deptName;
    }

    private String doFindDeptId(String dealUnitName) {
        if(StrUtil.isEmpty(dealUnitName)) {
            return null;
        }
        if(dealUnitName.contains("-")) {
            String[] split = dealUnitName.split("-");
            String parentId = this.findDeptId(split[0]);
            if(StrUtil.isEmpty(parentId)) {
                return null;
            }
            String deptId = this.findDealDeptIdByParentIdAndName(parentId, split[1]);
            return deptId;
        }
        return this.findDeptId(dealUnitName);
    }

    private String findDeptId(String deptName) {
        List<String> list = sysDeptMapper.selectDeptIdByName(deptName);
        if(CollectionUtil.isEmpty(list) || list.size() > 1) {
            return null;
        }
        return list.get(0);
    }

    private String findDealDeptIdByParentIdAndName(String parentId, String deptName) {
        String deptId = sysDeptMapper.selectDeptIdByParentIdAndName(parentId, deptName);
        if(StrUtil.isEmpty(deptId)) {
            return null;
        }
        return deptId;
    }

}
